from typing import Optional, List, Sequence, Any

from sqlalchemy import delete, update, select, and_, desc, asc, func, CursorResult, Result
from sqlalchemy.ext.asyncio import AsyncSession

from app.models.demo import PythonModel


async def insert(db: AsyncSession, entity: PythonModel) -> int:
    """
    插入数据

    :param db: 数据库会话对象
    :param entity: 待插入的数据
    :return: 成功数据条数
    """
    db.add(entity)
    return 1


async def delete_by_id(db: AsyncSession, id: int) -> int:
    """
    根据id删除数据

    :param db: 数据库会话对象
    :param id: 主键
    :return: 成功数据条数
    """
    # 构造删除语句
    stmt = delete(PythonModel).where(PythonModel.id == id)

    # 执行删除语句
    result = await db.execute(stmt)
    return result.rowcount


async def delete_by_ids(db: AsyncSession, ids: list[int]) -> int:
    """
    根据id批量删除数据

    :param db: 数据库会话对象
    :param ids: 主键列表
    :return: 成功数据条数
    """
    # 构造删除语句
    stmt = delete(PythonModel).where(PythonModel.id.in_(ids))

    # 执行删除语句
    result = await db.execute(stmt)
    return result.rowcount


async def delete_by_entity_id(db: AsyncSession, entity: PythonModel) -> int:
    """
    根据entity_id删除数据

    :param db: 数据库会话对象
    :param entity: 实体类对象
    :return: 成功数据条数
    """
    # 执行删除
    await db.delete(entity)
    return 1


async def update_by_id(db: AsyncSession, entity: PythonModel) -> int:
    """
    根据id修改数据

    :param db: 数据库会话对象
    :param entity: 实体类对象，id不能为空
    :return: 成功数据条数
    """
    # 构造修改语句
    stmt = update(PythonModel).where(PythonModel.id == entity.id).values(
        name=entity.name,
        code=entity.code,
        birthday=entity.birthday,
        describe=entity.describe
    )

    # 执行修改语句
    result = await db.execute(stmt)
    return result.rowcount


async def select_by_id(db: AsyncSession, id: int) -> PythonModel | None:
    """
    根据id查询数据

    :param db: 数据库会话对象
    :param id: 主键
    :return: 查询到的数据，未找到返回 None
    """
    # 构造查询语句
    stmt = select(PythonModel).where(PythonModel.id == id)

    # 执行查询
    result = await db.execute(stmt)
    return result.scalar_one_or_none()


async def select_by_ids(db: AsyncSession, ids: List[int]) -> Sequence[PythonModel]:
    """
    根据id批量查询数据

    :param db: 数据库会话对象
    :param ids: 主键列表
    :return: 查询到的数据
    """
    # 构造查询语句
    stmt = select(PythonModel).where(PythonModel.id.in_(ids))

    # 执行查询
    result = await db.execute(stmt)
    return result.scalars().all()


async def select_all(db: AsyncSession, entity: PythonModel) -> Sequence[PythonModel]:
    """
    根据条件查询数据

    :param db: 数据库会话对象
    :param entity: 查询条件
    :return: 查询到的数据
    """
    stmt = select(PythonModel)

    # 获取实体对象的属性并构建条件
    conditions = []
    for column in PythonModel.__mapper__.c.keys():
        # 获取entity每个字段的值
        value = getattr(entity, column, None)
        if value is None:
            continue
        conditions.append(getattr(PythonModel, column) == value)

    if conditions:
        stmt = stmt.where(*conditions)

    # 执行查询
    result = await db.execute(stmt)
    return result.scalars().all()


async def page(
    db: AsyncSession,
    page: int,
    limit: int,
    filters: Optional[list] = None,
    sort_field: Optional[str] = None,
    order_field: Optional[str] = "desc"
) -> tuple[Sequence[PythonModel], int]:
    """
    分页查询

    :param db: 数据库会话对象
    :param page: 页码（第几页）
    :param limit: 每页多少条数据
    :param filters: 查询过滤条件
    :param sort_field: 排序字段名
    :param order_field: 排序方式，可选asc、desc
    :return: (查询结果列表, 总记录数)
    """
    # 计算分页偏移量
    _page = max(page, 1)  # 保证page大于0
    offset = (_page - 1) * limit

    # 构造查询语句
    query = select(PythonModel)
    count_query = select(func.count(PythonModel.id))

    # 添加过滤条件
    if filters:
        query = query.where(and_(*filters))
        count_query = count_query.where(and_(*filters))

    # 查询总数
    result = await db.execute(count_query)
    count = result.scalar()

    # 添加排序条件
    if sort_field and order_field:
        if order_field == "desc" or order_field == "descending":
            query = query.order_by(desc(getattr(PythonModel, sort_field)))
        else:
            query = query.order_by(asc(getattr(PythonModel, sort_field)))

    # 分页查询
    stmt = query.offset(offset).limit(limit)
    result = await db.execute(stmt)
    items = result.scalars().all()

    return items, count
